相信在開發過程中應該有碰過偶爾要等後端工程師開api之後再串接
像是昨天要串API 也是因為沒有自己的API所以才找個其他人寫好的API端口當範例
那今天會用Google Apps Script做一個簡易後端 來跟後端工程師搶飯碗
來加速開發

不過因為寫的是簡易後台 回傳資料是寫死的 也不會拿去串接DB
所以基本上就是規格開完以後 你家後端工程師忙到爆炸 你自己想偷跑時方便串接用的

那麼廢話不多說 先去google雲端硬碟新增一個試算表

資料大概長這樣

懶的建置可以直接點我下載

接著點選工具->指令碼編輯器

會看到下面的畫面

程式碼

function doGet() {
  var result ={};
  result.web =[];
  result.mobile =[];
  return  ContentService.createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON);
}

完成後儲存
另外 Autocomplete 在編輯 ->內容協助 (快捷顯示CTRL+SPACE但我按了沒反應)

接著點選
發布 -> 部屬為網路應用程式

設定

完成後部屬

部屬成功後

複製url
或是點選連結看結果

result

{"web":[],"mobile":[]}

接著來binding 資料

code:

function doGet() {
  var result ={};
  
  var mobile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mobile')
    .getDataRange()
    .getValues();
  
   var web = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Web')
    .getDataRange()
    .getValues();
  
  result.web = makeObject(web);
  result.mobile = makeObject(mobile);
  
  Logger.log(result.web);
//  return  ContentService.createTextOutput(JSON.stringify(result))
//  .setMimeType(ContentService.MimeType.JSON);
}

function makeObject(multiArr){
  var obj = {};
  var headers = multiArr.shift();
  for(var i =0 ; i<headers.length; i++){
    obj[headers[i]] = multiArr.map(function(app) {
      return app[i];
    });
  }
      
  return obj;
}

完成後先別發布 直接運行

看到授權都允許就對了

接著去
查看 -> 紀錄
可以看到運行的結果(log)

沒問題後把log取消 換回return

function doGet() {
  var result ={};
  
  var mobile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mobile')
    .getDataRange()
    .getValues();
  
   var web = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Web')
    .getDataRange()
    .getValues();
  
  result.web = makeObject(web);
  result.mobile = makeObject(mobile);
  
  return  ContentService.createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON);
}

function makeObject(multiArr){
  var obj = {};
  var headers = multiArr.shift();
  for(var i =0 ; i<headers.length; i++){
    obj[headers[i]] = multiArr.map(function(app) {
      return app[i];
    });
  }
      
  return obj;
}

然後部屬
接著你應該可以看到類似這樣的回傳值

{"web":{"Web Graphic":["Adobe Spark","Google Draw","Sumo Paint","Draw.io","Awwapp","Canva"],"Web Photos":["Fotor","PicMonkey","Pixlr","LunaPic","Google Photos","Polaar"],"Web Video":["Wevideo","PowToon","stop motion animation","Adobe Spark","Animoto","Magisto"],"Web Autio":["Twisted Wave","Soundtrap","AudioTool","Chirbit","Incredibox","Bosca Ceoil"],"Web Writing":["Google Docs","Google Slides","Google Keep","Blogger","Storyboard","Flippity MaadLibs"],"Web Review":["Flippity Quiz","Kahoot","Quizziz","Flippity Badge","Google Forms","Flippity"],"Web Silly":["Bitmoji","Meme Generator","Google Photos","Emojipedia","Giphy","frinkiac"],"Web Publication":["Blogger","Google Slides","Movenote","Google Sites","Book Creator","Flipgrid"]},"mobile":{"Mobile Writing":["Google Docs","Google Slides","Blogger","Google Keep","Simplenote","Evernote"],"Mobile Photos":["Prisma","Photoshop Mix","Pixlr","Snapseed","Google Photos","Desygner"],"Mobile Graphics":["Adobe lllustrator","Google Keep","AutoDesk Sketchbook","Simple Mind","Awwapp","LensooCreate"],"Mobile Audio":["Cross DJ Free","Soundtrap","Music Maker Jam","Cross DJ Free","Soundtrap","Music Maker Jam"],"Mobile Video":["We Video","Magisto","Lapselt","Google Photos","Magisto","Lapselt"],"Mobile Silly":["Bitmoji","Meme Generator","Motion Stills","Gboard","Giphycam","Frinkiac"]}}

接著新增一個新的script 並且改為post形式來接收參數

PostExample

function doPost(e)  {
  
  var search = e.parameter.search;
  
  var count = e.parameter.count;
  var URL = 'formUrl'
  
  var response = UrlFetchApp.fetch(URL);
  
  var parsedResponse = JSON.parse(response);
  var ss = parsedResponse.mobile[search];
  
//  Logger.log(parsedResponse.mobile[search]);
 var json = {
    'resultCode':200, 
    'DB': ss[count]
}; 
  
var result ={};
  result.web =[];
  result.mobile =[];
  return  ContentService.createTextOutput(JSON.stringify(json))
  .setMimeType(ContentService.MimeType.JSON);
}

formUrl為之前部屬的script的url

接著用postman來呼叫看看 (當然你可以使用其他方式post)

這樣就實作完簡易的post get api啦~~
apps script還有很多更複雜的用法 有興趣可以自行去看看別人的範例